using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Toenda.Rails.Data;
using Toenda.Rails.DataContainer;
using Toenda.TFL.Base;
using Toenda.TFL.Data;

namespace Toenda.Rails {
	namespace Data {
		/// <summary>
		/// Class DBMaintenance
		/// </summary>
		public class DBMaintenance : DBClient {
			// ---------------------------------------------------
			// CONSTRUCTORS
			// ---------------------------------------------------

			/// <summary>
			/// Default Ctor
			/// </summary>
			public DBMaintenance() {
			}

			// ---------------------------------------------------
			// INTERFACE IMPLEMENTATIONS
			// ---------------------------------------------------

			// ---------------------------------------------------
			// PROPERTIES
			// ---------------------------------------------------

			// ---------------------------------------------------
			// EVENTS
			// ---------------------------------------------------

			// ---------------------------------------------------
			// PRIVATE MEMBERS
			// ---------------------------------------------------

			// ---------------------------------------------------
			// PROTECTED MEMBERS
			// ---------------------------------------------------

			// ---------------------------------------------------
			// PUBLIC MEMBERS
			// ---------------------------------------------------

			/// <summary>
			/// Check all sessions in database and delete all older as 4 (!!!) hours
			/// </summary>
			/// <param name="hours">The amount of hours.</param>
			public void CheckSessions(int hours) {
				StringBuilder str = new StringBuilder();

				str.Append("DELETE FROM [dbo].[tbl_session]");
				str.Append(" WHERE [date] < dateadd(hour, -" + hours.ToString() + ", getdate())");

				int nResult = base.DBBaseClass.ExecuteNonQuery(str.ToString());
			}

			/// <summary>
			/// Clean the usertable
			/// </summary>
			public void CleanUserTable() {
				StringBuilder str = new StringBuilder();

				str.Append("DELETE FROM [dbo].[tbl_user]");
				str.Append(" WHERE ( username = '' OR username IS NULL )");
				str.Append(" AND ( email = '' OR email IS NULL )");

				int nResult = base.DBBaseClass.ExecuteNonQuery(str.ToString());
			}

			/// <summary>
			/// Clean the usertable
			/// </summary>
			public void CleanUpProAccounts() {
				StringBuilder str = new StringBuilder();

				str.Append("UPDATE tbl_user ");
				str.Append("SET group_pkid = '178efc30-5a67-472f-a1e2-e0f0f97f1c97', ");
				str.Append("pro_start_date = NULL, ");
				str.Append("pro_duration = NULL ");
				str.Append("WHERE DATEADD(YY, pro_duration, pro_start_date) < getdate()");

				int nResult = base.DBBaseClass.ExecuteNonQuery(str.ToString());
			}

			/// <summary>
			/// Get all info about the deletable users
			/// </summary>
			/// <param name="amountOfMonths"></param>
			/// <returns></returns>
			public List<List<string>> GetDeleteableUserInfo(int amountOfMonths) {
				List<string> list_images = new List<string>();
				List<string> list_user = new List<string>();
				DataSet img;
				DataTableReader rd;

				StringBuilder str = new StringBuilder();

				// get old users
				str.Append("SELECT pkid, avatar FROM [dbo].[tbl_user]");
				str.Append(" WHERE [register_date] < dateadd(mm, -" + amountOfMonths.ToString() + ", getdate())");
				str.Append(" AND enabled = 0");

				DataSet data = base.DBBaseClass.ExecuteQuery(str.ToString());

				if(data.Tables[0].Rows.Count > 0) {
					DataTableReader reader = data.CreateDataReader();

					while(reader.Read()) {
						if(!reader.IsDBNull(reader.GetOrdinal("avatar"))) {
							list_user.Add(reader["avatar"].ToString());
						}

						str.Remove(0, str.Length);
						str.Append("SELECT pkid FROM [dbo].[tbl_images]");
						str.Append(" WHERE user_pkid = '" + reader["pkid"].ToString() + "'");

						img = base.DBBaseClass.ExecuteQuery(str.ToString());

						if(img.Tables[0].Rows.Count > 0) {
							rd = img.CreateDataReader();

							while(rd.Read()) {
								list_images.Add(rd["pkid"].ToString());
							}
						}
					}
				}

				List<List<string>> list = new List<List<string>>();
				list.Add(list_images);
				list.Add(list_user);

				return list;
			}

			/// <summary>
			/// Delete all users who are older as a specific amount of months
			/// </summary>
			/// <param name="amountOfMonths">The specific amount of months</param>
			/// <returns>A list of all id of the images to delete.</returns>
			public void DeleteAllNonActivatedUser(int amountOfMonths) {
				StringBuilder str = new StringBuilder();

				// get old users
				str.Append("SELECT * FROM [dbo].[tbl_user]");
				str.Append(" WHERE [register_date] < dateadd(mm, -" + amountOfMonths.ToString() + ", getdate())");
				str.Append(" AND enabled = 0");

				DataSet data = base.DBBaseClass.ExecuteQuery(str.ToString());

				if(data.Tables[0].Rows.Count > 0) {
					DataTableReader reader = data.CreateDataReader();

					while(reader.Read()) {
						str.Remove(0, str.Length);
						str.Append("DELETE FROM [dbo].[tbl_images]");
						str.Append(" WHERE user_pkid = '" + reader["pkid"].ToString() + "'");

						base.DBBaseClass.ExecuteNonQuery(str.ToString());

						str.Remove(0, str.Length);
						str.Append("DELETE FROM [dbo].[tbl_user_settings]");
						str.Append(" WHERE user_pkid = '" + reader["pkid"].ToString() + "'");

						base.DBBaseClass.ExecuteNonQuery(str.ToString());

						str.Remove(0, str.Length);
						str.Append("DELETE FROM [dbo].[tbl_sets]");
						str.Append(" WHERE user_pkid = '" + reader["pkid"].ToString() + "'");

						base.DBBaseClass.ExecuteNonQuery(str.ToString());
					}
				}

				// delete user
				str.Remove(0, str.Length);
				str.Append("DELETE FROM [dbo].[tbl_user]");
				str.Append(" WHERE [register_date] < dateadd(mm, -" + amountOfMonths.ToString() + ", getdate())");
				str.Append(" AND enabled = 0");

				base.DBBaseClass.ExecuteNonQuery(str.ToString());
			}

			/// <summary>
			/// Make a backup of the database
			/// </summary>
			public void MakeBackup() {
			}
		}
	}
}
